# -*- coding: utf-8 -*-
__author__ = 'sunnychou'
__date__ = '2019/7/29 10:20'
from time import strftime, localtime
from datetime import timedelta, date
import datetime
import calendar
#from datetime import datetime
from common.mssql_helper import g_msql_inst
from k3_data_report.finance.config import finace_cal_fields
from collections import defaultdict
'''
获取指定日期的上个月
日期字符串和日期格式
'''
def getLastMonth(dtstr, dateformat):

    d = datetime.strptime(dtstr, dateformat).date()
    year = d.year
    month = d.month
    if month == 1:  # 如果是本年1月的
        month = 12
        year -= 1
    else:  # 如果是大于1月的
        month -= 1
    return (datetime(year, month, 1)).strftime(dateformat)


def store_finance_tb_db(finance_rp_sql, value_tuple):
    try:
        excuete_sql = finance_rp_sql % value_tuple
        print(excuete_sql)
        g_msql_inst.db_excuete(excuete_sql)
        return True
    except Exception as e:
        print(f"store_finance_tb_db has error with {e}")
        return False



def convert_str_float(snumber):
    try:
        return float("%.2f" % float(snumber))
    except Exception as e:
        return 0


def cal_finance_jine_select(select_sql):
    finace_jine_list = g_msql_inst.fetch_all(select_sql)
    finace_jine_dict = {}
    for (dingdane, identified_code) in finace_jine_list:
        finace_jine_dict[identified_code] =  convert_str_float(dingdane)
    return finace_jine_dict


def check_month_data_exist(tb_type):
    '''
    判断月份数据是否存在
    :param tb_type:
    :return:
    '''
    tb_exist_select_sql = finace_cal_fields['crm_k3_month_tb_exist'][tb_type]
    caigoud_jine_list = g_msql_inst.fetch_all(tb_exist_select_sql)
    cg_month_set = set()
    if caigoud_jine_list == None or len(caigoud_jine_list) == 0:
        return cg_month_set
    for cg_month_tp in caigoud_jine_list:
        cg_month = cg_month_tp[0]
        cg_month_set.add(cg_month)
    return cg_month_set


def get_exvalue(excel_data_dict, index):
    try:
        value = excel_data_dict[index]
        if  value == "":
            return 0
        return value
    except Exception as e:
        return 0



def charge_cg_yingfuk_by_jiesuan_type(last_month):
    '''
    采购部分   ---- 针对供应商
           计算本月应付款
           根据采购订单中结算方式
                    "款到发货" 或者 "款到订货":    ---》  从采购订单获取 价税合计字段
                    "月结" 或者 "月结30天/货到票到30天付款":       ---》  从采购发票获取 价税合计 字段
                    "货到付款": 3   ---  从外购入库获取    ----》 从外购入库 获取 含税金额 字段
    '''
    yingfuk_result_dict = {}
    # if jiesuan_type == "款到发货": 1
    yingfuk_by_kdfh_bymonth_select_sql = finace_cal_fields['jine_by_jiesuan_type']['yingfuk_by_kdfh_bymonth'] % last_month
    yingfuk_by_kdfh_bymonth_dict = cal_finance_jine_select(yingfuk_by_kdfh_bymonth_select_sql)
    #从采购订单获取 价税合计字段
    yingfuk_result_dict[1] = yingfuk_by_kdfh_bymonth_dict
    # elif re.search(r'月结',jiesuan_type) != None:  2
    yingfuk_with_yuejie_bymonth_select_sql = finace_cal_fields['jine_by_jiesuan_type'][
                                                 'yingfuk_with_yuejie_bymonth'] % last_month
    yingfuk_with_yuejie_bymonth_dict = cal_finance_jine_select(yingfuk_with_yuejie_bymonth_select_sql)
    #从采购发票获取 价税合计 字段
    yingfuk_result_dict[2] = yingfuk_with_yuejie_bymonth_dict

    yingfuk_with_waigou_bymonth_select_sql = finace_cal_fields['jine_by_jiesuan_type'][
                                                 'yingfuk_with_waigou_bymonth'] % last_month
    yingfuk_with_waigou_bymonth_dict = cal_finance_jine_select(yingfuk_with_waigou_bymonth_select_sql)
    #从外购入库 获取 含税金额 字段
    yingfuk_result_dict[3] = yingfuk_with_waigou_bymonth_dict
    return yingfuk_result_dict



def charge_xs_ysk_by_jiesuan_type(last_month):
    '''
    销售部分   ---- 针对客户
           计算本月应收款
           根据收款单中结算方式进行判断
                    "款到发货" 或者 "款到订货":    ---》  从销售订单计算 价税合计字段
                    "月结" 或者 "月结30天/货到票到30天付款":       ---》  从销售发票获取 价税合计 字段
                    "货到付款": 3   ---  从销售出库获取    ----》 从销售出库 获取 含税金额 字段

            操作策略：
                 因为有可能一个订单，比如款到发货的，可能在销售订单里面，也有可能在销售发票里面，同时也有可能在销售出库里面

                 可以采取顺序遍历
    '''
    ysk_result_dict = {}
    # if jiesuan_type == "款到发货": 1
    ysk_by_kdfh_bymonth_select_sql = finace_cal_fields['jine_by_jiesuan_type']['ysk_by_kdfh_bymonth'] % last_month
    ysk_by_kdfh_bymonth_dict = cal_finance_jine_select(ysk_by_kdfh_bymonth_select_sql)
    #从采购订单获取 价税合计字段
    ysk_result_dict[1] = ysk_by_kdfh_bymonth_dict
    # elif re.search(r'月结',jiesuan_type) != None:  2
    ysk_by_mtyj_bymonth_select_sql = finace_cal_fields['jine_by_jiesuan_type']['ysk_by_mtyj_bymonth'] % last_month
    ysk_by_mtyj_bymonth_dict = cal_finance_jine_select(ysk_by_mtyj_bymonth_select_sql)
    #从销售发票获取 价税合计 字段
    ysk_result_dict[2] = ysk_by_mtyj_bymonth_dict

    ysk_with_xsck_bymonth_select_sql = finace_cal_fields['jine_by_jiesuan_type']['ysk_by_hdfk_bymonth'] % last_month
    ysk_with_xsck_bymonth_dict = cal_finance_jine_select(ysk_with_xsck_bymonth_select_sql)
    #从外购入库 获取 含税金额 字段
    ysk_result_dict[3] = ysk_with_xsck_bymonth_dict
    return ysk_result_dict


def  ysk_maoli_by_ydtype(last_month):
    '''
    获取本月毛利，根据收款单中的源单类型
    :param last_month:
    :return:
    '''
    ysk_maoli_select_sql = finace_cal_fields['kh_business']['sdk_ml_base_select'] % last_month
    ysk_maoli_base_list = g_msql_inst.fetch_all(ysk_maoli_select_sql)
    ysk_kehu_info_dict = defaultdict(list)

    for   (kh, ydlx, ggxh, jsssje, sl, bb)  in   ysk_maoli_base_list:
        sdk_ml_info_sql = finace_cal_fields['kh_business']['sdk_ml_cgdd_select'] if ydlx == "销售订单" \
            else finace_cal_fields['kh_business']['sdk_ml_xsck_select']
        skd_ml_res_list = g_msql_inst.fetch_all(sdk_ml_info_sql % ggxh)
        (ws_uprice, slv) = skd_ml_res_list[0]   if len(skd_ml_res_list) > 0 else (0,0)
        #slv =   slv   if  bb == '人民币' else  slv *
        print(f"kehu:{kh}, slv:{slv}, type:{type(slv)}, jsssje:{jsssje}")
        slv = float(slv)
        slv_rate = float(1.0 + slv/100.0)
        kh_skd_jine = float("%.2f" %  float(float(jsssje) / slv_rate  - float(float(ws_uprice) * float(sl))))
        ysk_kehu_info_dict[kh].append(kh_skd_jine)

    return ysk_kehu_info_dict


def cal_ysk_by_timerange(begin_time, end_time):
    '''
    按照时间维度计算应收款金额
    计算本月应收款
           根据收款单中结算方式进行判断
                    "款到发货" 或者 "款到订货":    ---》  从销售订单计算 价税合计字段
                    "月结" 或者 "月结30天/货到票到30天付款":       ---》  从销售发票获取 价税合计 字段
                    "货到付款": 3   ---  从销售出库获取    ----》 从销售出库 获取 含税金额 字段
            按照日期将三个条件上的数据都获取到
    :param begin_time: 开始时间   2019-07-01
    :param end_time:  结束时间    2019-09-30
    :return:
    '''
    ysk_result_dict = {}
    #if re.search(r"货到", jiesuan_type) != None:  # 货到,销售出库
    ysk_with_xsck_bymonth_select_sql = finace_cal_fields['jine_by_jiesuan_type']['ysk_by_hdfk_byrange'] % (begin_time, end_time)
    ysk_with_xsck_bymonth_dict = cal_finance_jine_select(ysk_with_xsck_bymonth_select_sql)
    # 从外购入库 获取 含税金额 字段
    ysk_result_dict[1] = ysk_with_xsck_bymonth_dict
    #elif re.search(r"月结", jiesuan_type) != None:  # 从采购发票获取
    ysk_by_mtyj_bymonth_select_sql = finace_cal_fields['jine_by_jiesuan_type']['ysk_by_mtyj_bytrange'] % (begin_time, end_time)
    ysk_by_mtyj_bymonth_dict = cal_finance_jine_select(ysk_by_mtyj_bymonth_select_sql)
    # 从销售发票获取 价税合计 字段
    ysk_result_dict[2] = ysk_by_mtyj_bymonth_dict
    #else:            #款到，现金
    ysk_by_kdfh_bymonth_select_sql = finace_cal_fields['jine_by_jiesuan_type']['ysk_by_kdfh_bytrange'] % (begin_time, end_time)
    ysk_by_kdfh_bymonth_dict = cal_finance_jine_select(ysk_by_kdfh_bymonth_select_sql)
    ysk_result_dict[3] = ysk_by_kdfh_bymonth_dict
    return ysk_result_dict


def fetch_lastyear_ljqk():
    lastyear_qk_sql = finace_cal_fields['lastyear_qk']["select_lastyear_qk"]
    lastyear_qk_list = g_msql_inst.fetch_all(lastyear_qk_sql)
    lastyear_qk_dict = {}
    for (kh_code,  lj2017qk, lj2018qk) in lastyear_qk_list:
        lastyear_qk_dict[kh_code] = (lj2017qk, lj2018qk)
    return lastyear_qk_dict


if __name__ == "__main__":
    #获取上个月
    #print(get_today_month(-4))
    #res = getMonthFirstDayAndLastDay()
    #print(res)
    lastyear_qk_dict = fetch_lastyear_ljqk()
    print(lastyear_qk_dict)
